import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.http.HttpRequest;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import com.ww.springboot.boot.utils.ObjectUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 描述：
 *
 * @author 🧑 ‍wanwei
 * @since 2025-09-10 10:08
 */
@Slf4j
public class EsExportTestMonthCommon {

    public static void main(String[] args) throws IOException, SQLException, InvalidFormatException {
        ClassPathResource classPathResource = new ClassPathResource("/static/text/month.txt");
        String resp = IOUtils.toString(classPathResource.getInputStream(), "utf-8");

        JSONObject jsonObject = JSONObject.parseObject(resp);
        JSONArray jsonArray = jsonObject.getJSONObject("aggregations").getJSONObject("by_create_time").getJSONArray("buckets");
        List<List<String>> list = new ArrayList<>();
        HashSet<String> hashSet = new HashSet();
        for (Object o : jsonArray) {
            String date = ((JSONObject) o).getString("key_as_string");
            JSONArray jsonArray1 = ((JSONObject) o).getJSONObject("package_goods").getJSONObject("by_pms_sku").getJSONArray("buckets");
            for (Object o1 : jsonArray1){
                String sku = ((JSONObject) o1).getString("key");
                Integer quantity = ((JSONObject) o1).getJSONObject("total_quantity").getInteger("value");
                List<String> list1 = new ArrayList<>();
                list1.add(date);
                list1.add(sku);
                list1.add(quantity.toString());
                list.add(list1);
                hashSet.add(sku);
            }
        }

        Connection connection = DriverManager.getConnection("jdbc:mysql://10.50.40.18:3306/cpms", "wanwei", "AM2618@01@A&pkType");
        String skuList = hashSet.stream().collect(Collectors.toList()).stream().collect(Collectors.joining("','"));
        String sql = "select product_sku,product_name,main_graph_url from pms_sku where product_sku in ('"+skuList+"')";
        ResultSet resultSet = SqlExecutor.callQuery(connection, sql);
        Map<String,List<String>> map = new HashMap<>();
        while (resultSet.next()) {
            int length = ((ResultSetImpl) resultSet).getMetadata().getFields().length;
            List<String> listValue = new ArrayList<>();
            for (int i = 1; i <= length; i++) {
                listValue.add(resultSet.getString(i));
            }
            map.put(listValue.get(0),listValue);
        }
        connection.close();

        for (List<String> one : list) {
            String sku = one.get(1);
            List<String> list1 = map.get(sku);
            one.add(list1.get(1));
            one.add(list1.get(2));
        }
        String fileName = "D:\\MyFile\\ZJ00014\\zj014\\Desktop\\test\\SQL导出" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(new File(fileName))
                .sheet("")
                .doWrite(list);
        链接转图片(new File(fileName));
    }

    private static void 链接转图片(File file) throws IOException, InvalidFormatException {
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        int numberOfSheets = workbook.getNumberOfSheets();

        for (int i = 0; i < numberOfSheets; i++) {
            XSSFSheet sheetAt = workbook.getSheetAt(i);
            int row = 1;
            while (true) {
                XSSFRow row1 = sheetAt.getRow(row);
                if (row1 == null) {
                    break;
                }
                int cellNum = 4;
                String url = "";
                while (cellNum< 30) {
                    XSSFCell cell = row1.getCell(cellNum);
                    if (cell == null){
                        cellNum++;
                        continue;
                    }
                    String value = cell.getStringCellValue();
                    if (value.startsWith("http")) {
                        url = value;
                        break;
                    }
                    cellNum++;
                }
                if(ObjectUtil.isEmpty(url)){
                    System.out.println("图片链接为空：" + row +"--"+ numberOfSheets);
                    row++;
                    continue;
                }
                url += "?x-oss-process=image/resize,h_500,w_500/quality,q_50";
                System.out.println(url);
                try{
                    byte[] bytes = HttpRequest.get(url).execute().bodyBytes();
                    //anchor主要用于设置图片的属性
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                    CreationHelper helper = workbook.getCreationHelper();
                    Drawing drawing = sheetAt.createDrawingPatriarch();
                    ClientAnchor anchor = helper.createClientAnchor();
                    // 图片插入坐标
                    anchor.setCol1(++cellNum);
                    anchor.setRow1(row);
                    // 插入图片
                    Picture pict = drawing.createPicture(anchor, pictureIdx);
                    pict.resize(1, 1);
                }catch (Exception e){
                    log.error("图片插入失败：" + url,e);
                }
                row++;
            }
        }
        // 指定要写入的文件路径
        String filePath = file.getParent() +"/"+ file.getName().substring(0, file.getName().lastIndexOf(".")) + "新.xlsx";
        // 使用 FileOutputStream 将 XSSFWorkbook 写入文件
        try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
            workbook.write(fileOut);
            System.out.println("Excel 文件已成功写入: " + filePath);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭 workbook 释放资源
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
